#!/usr/bin/env python3
"""
修复数据库表结构，解决数据类型不匹配问题
运行：python scripts/fix_database_schema.py
"""

import sys
from pathlib import Path

# 确保项目根目录在 sys.path
ROOT = Path(__file__).resolve().parents[1]
if str(ROOT) not in sys.path:
    sys.path.insert(0, str(ROOT))

from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from app.core.database import engine


def drop_existing_tables():
    """删除现有的表（按依赖关系顺序）"""
    
    tables_to_drop = [
        "conversation_stats",
        "messages", 
        "conversations",
        "users"
    ]
    
    triggers_to_drop = [
        "update_conversation_stats_on_insert"
    ]
    
    try:
        with engine.connect() as conn:
            print("🗑️  删除现有表和触发器...")
            
            # 删除触发器
            for trigger in triggers_to_drop:
                try:
                    conn.execute(text(f"DROP TRIGGER IF EXISTS {trigger}"))
                    print(f"  ✅ 删除触发器: {trigger}")
                except SQLAlchemyError as e:
                    print(f"  ⚠️  删除触发器 {trigger} 失败: {e}")
            
            # 删除表
            for table in tables_to_drop:
                try:
                    conn.execute(text(f"DROP TABLE IF EXISTS {table}"))
                    print(f"  ✅ 删除表: {table}")
                except SQLAlchemyError as e:
                    print(f"  ⚠️  删除表 {table} 失败: {e}")
            
            conn.commit()
            print("✅ 现有表删除完成！")
            
    except SQLAlchemyError as e:
        print(f"❌ 删除表失败: {e}")
        return False
    
    return True


def create_tables_with_correct_types():
    """使用正确的数据类型创建表"""
    
    sql_statements = [
        # 创建用户表
        """
        CREATE TABLE users (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
            username VARCHAR(50) NOT NULL COMMENT '用户名',
            password VARCHAR(256) NOT NULL COMMENT '密码哈希',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
            PRIMARY KEY (id),
            UNIQUE KEY uk_username (username),
            INDEX idx_username (username)
        ) ENGINE = InnoDB
          DEFAULT CHARSET = utf8mb4
          COLLATE = utf8mb4_unicode_ci
          COMMENT = '用户信息表'
        """,
        
        # 创建会话表
        """
        CREATE TABLE conversations (
            id VARCHAR(36) NOT NULL COMMENT '会话ID（UUID）',
            user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
            title VARCHAR(200) NOT NULL DEFAULT '新的聊天' COMMENT '会话标题',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
            is_deleted TINYINT(1) DEFAULT 0 COMMENT '是否已删除（软删除）',
            PRIMARY KEY (id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            INDEX idx_user_id (user_id),
            INDEX idx_created_at (created_at),
            INDEX idx_user_created (user_id, created_at)
        ) ENGINE = InnoDB
          DEFAULT CHARSET = utf8mb4
          COLLATE = utf8mb4_unicode_ci
          COMMENT = '用户会话表'
        """,
        
        # 创建消息表
        """
        CREATE TABLE messages (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '消息ID',
            conversation_id VARCHAR(36) NOT NULL COMMENT '会话ID',
            sender ENUM('user', 'bot', 'system') NOT NULL COMMENT '发送者类型',
            content TEXT NOT NULL COMMENT '消息内容',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            message_order INT UNSIGNED NOT NULL COMMENT '消息在会话中的顺序',
            PRIMARY KEY (id),
            FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
            INDEX idx_conversation_id (conversation_id),
            INDEX idx_conversation_order (conversation_id, message_order),
            INDEX idx_created_at (created_at)
        ) ENGINE = InnoDB
          DEFAULT CHARSET = utf8mb4
          COLLATE = utf8mb4_unicode_ci
          COMMENT = '聊天消息表'
        """,
        
        # 创建会话统计表
        """
        CREATE TABLE conversation_stats (
            conversation_id VARCHAR(36) NOT NULL COMMENT '会话ID',
            message_count INT UNSIGNED DEFAULT 0 COMMENT '消息总数',
            last_message_at TIMESTAMP NULL COMMENT '最后一条消息时间',
            PRIMARY KEY (conversation_id),
            FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
        ) ENGINE = InnoDB
          DEFAULT CHARSET = utf8mb4
          COLLATE = utf8mb4_unicode_ci
          COMMENT = '会话统计表'
        """
    ]
    
    # 触发器SQL
    create_trigger_sql = """
    CREATE TRIGGER update_conversation_stats_on_insert
    AFTER INSERT ON messages
    FOR EACH ROW
    BEGIN
        INSERT INTO conversation_stats (conversation_id, message_count, last_message_at)
        VALUES (NEW.conversation_id, 1, NEW.created_at)
        ON DUPLICATE KEY UPDATE
            message_count = message_count + 1,
            last_message_at = NEW.created_at;
    END
    """
    
    try:
        with engine.connect() as conn:
            print("🚀 创建表结构...")
            
            # 执行表创建语句
            for i, sql in enumerate(sql_statements, 1):
                try:
                    conn.execute(text(sql))
                    print(f"  ✅ 步骤 {i}: 表创建成功")
                except SQLAlchemyError as e:
                    print(f"  ❌ 步骤 {i}: {e}")
                    raise
            
            # 创建触发器
            try:
                conn.execute(text(create_trigger_sql))
                print("  ✅ 触发器创建成功")
            except SQLAlchemyError as e:
                print(f"  ⚠️  触发器创建失败: {e}")
            
            conn.commit()
            print("✅ 所有表创建完成！")
            
    except SQLAlchemyError as e:
        print(f"❌ 表创建失败: {e}")
        return False
    
    return True


def verify_tables():
    """验证表是否创建成功"""
    print("\n🔍 验证表结构...")
    
    tables_to_check = ['users', 'conversations', 'messages', 'conversation_stats']
    
    try:
        with engine.connect() as conn:
            for table in tables_to_check:
                result = conn.execute(text(f"SHOW TABLES LIKE '{table}'"))
                if result.fetchone():
                    print(f"  ✅ 表 {table} 存在")
                    
                    # 显示表结构
                    desc_result = conn.execute(text(f"DESCRIBE {table}"))
                    columns = desc_result.fetchall()
                    print(f"     字段数: {len(columns)}")
                    
                    # 显示关键字段的类型
                    if table == 'users':
                        id_col = [col for col in columns if col[0] == 'id'][0]
                        print(f"     users.id 类型: {id_col[1]}")
                    elif table == 'conversations':
                        user_id_col = [col for col in columns if col[0] == 'user_id'][0]
                        print(f"     conversations.user_id 类型: {user_id_col[1]}")
                else:
                    print(f"  ❌ 表 {table} 不存在")
                    
    except SQLAlchemyError as e:
        print(f"❌ 验证失败: {e}")


def main():
    """主函数"""
    print("=" * 60)
    print("🔧 数据库表结构修复脚本")
    print("=" * 60)
    
    print("⚠️  警告：此操作将删除所有现有数据！")
    confirm = input("确认继续？(y/N): ")
    if confirm.lower() != 'y':
        print("操作已取消")
        return
    
    # 删除现有表
    if not drop_existing_tables():
        print("❌ 删除表失败，操作终止")
        sys.exit(1)
    
    # 创建新表
    if not create_tables_with_correct_types():
        print("❌ 创建表失败，操作终止")
        sys.exit(1)
    
    # 验证表结构
    verify_tables()
    
    print("\n🎉 数据库表结构修复完成！")
    print("💡 现在数据类型已经一致，可以正常使用了。")


if __name__ == "__main__":
    main()
